To reduce treatment delays caused by stock-outs and financial losses from expired medicines by improving pharmacy inventory planning using demand analysis, expiry monitoring, and reorder alerts.
1.Final_Pharmacy_Inventory_Extended.csv --> df_meds¶
- Key fields :Drug name, manufacturer, expiry date, stock quantity, days remaining, unit price, therapeutic class, etc.
2.Synthetic Sales Table (Jan 2024 – Dec 2025) --> df_med_sales¶
Created due to lack of sales data
key fields: date, drug ID, quantity sold, unit price, total sales amount
Database¶
Loaded into MySQL (pharma_inventory)
Inventory table: 256,476 rows × 15 columns
Sales table: 218,757 rows × 6 columns
Import Liabaries¶
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import plotly.express as px
import seaborn as sns
! pip install pandas sqlalchemy mysql-connector-python
from sqlalchemy import create_engine
import pandas as pd
Requirement already satisfied: pandas in c:\users\hp\anaconda3\lib\site-packages (2.2.2) Requirement already satisfied: sqlalchemy in c:\users\hp\anaconda3\lib\site-packages (2.0.30) Requirement already satisfied: mysql-connector-python in c:\users\hp\anaconda3\lib\site-packages (9.5.0) Requirement already satisfied: numpy>=1.26.0 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (1.26.4) Requirement already satisfied: python-dateutil>=2.8.2 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2.9.0.post0) Requirement already satisfied: pytz>=2020.1 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2024.1) Requirement already satisfied: tzdata>=2022.7 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2023.3) Requirement already satisfied: typing-extensions>=4.6.0 in c:\users\hp\anaconda3\lib\site-packages (from sqlalchemy) (4.15.0) Requirement already satisfied: greenlet!=0.4.17 in c:\users\hp\anaconda3\lib\site-packages (from sqlalchemy) (3.0.1) Requirement already satisfied: six>=1.5 in c:\users\hp\anaconda3\lib\site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)
Collect and Clean Data¶
df_meds=pd.read_csv('Final_Pharmacy_Inventory_Extended.csv')
df_meds.columns
Index(['Drug_Name', 'Manufacturer', 'Price', 'Active_Ingredient',
'Therapeutic_Class', 'Action_Class', 'Primary_Use', 'Is_Habit_Forming',
'Mfg_Date', 'Expiry_Date', 'Days_Remaining', 'Stock_Qty'],
dtype='object')
df_meds['Drug_Name'].value_counts()
Drug_Name
Ringer Lactate Infusion 28
NS 0.9% Infusion 24
Cefpoxim Dry Syrup 16
Oral Polio Vaccine 12
Broxine Syrup 12
..
Fenceta M Syrup 1
Flood 20mg Capsule 1
Flujoy Tablet 1
Fexomeg M 10mg/180mg Tablet 1
Zyvocol 1% Dusting Powder 1
Name: count, Length: 249398, dtype: int64
df_drugs_unique=df_meds.copy()
df_drugs_unique = (
df_drugs_unique[["Drug_Name"]]
.drop_duplicates()
.reset_index(drop=True)
)
df_drugs_unique["DrugID"] = range(1, len(df_drugs_unique) + 1)
df_drugs_unique
| Drug_Name | DrugID | |
|---|---|---|
| 0 | Augmentin 625 Duo Tablet | 1 |
| 1 | Azithral 500 Tablet | 2 |
| 2 | Ascoril LS Syrup | 3 |
| 3 | Allegra 120mg Tablet | 4 |
| 4 | Avil 25 Tablet | 5 |
| ... | ... | ... |
| 249393 | Ziyapod 100mg Oral Suspension | 249394 |
| 249394 | Zemhart 30mg Tablet | 249395 |
| 249395 | Zivex 25mg Tablet | 249396 |
| 249396 | ZI Fast 500mg Injection | 249397 |
| 249397 | Zyvocol 1% Dusting Powder | 249398 |
249398 rows × 2 columns
df_meds = df_meds.merge(df_drugs_unique, on="Drug_Name", how="left")
df_meds.columns
Index(['Drug_Name', 'Manufacturer', 'Price', 'Active_Ingredient',
'Therapeutic_Class', 'Action_Class', 'Primary_Use', 'Is_Habit_Forming',
'Mfg_Date', 'Expiry_Date', 'Days_Remaining', 'Stock_Qty', 'DrugID'],
dtype='object')
import numpy as np
import pandas as pd
df_meds_1 = df_meds.copy()
df_drugs_1 = df_drugs_unique.copy()
dates = pd.date_range(start="2024-01-01", periods=365*2)
sales_rows = []
np.random.seed(42)
for day in dates:
daily_sales_count = np.random.randint(100,500)
meds_today = (
df_meds_1.sample(daily_sales_count, replace=True)
.reset_index(drop=True)
)
qty_sold = np.random.randint(1, 15, size=daily_sales_count)
for idx, row in meds_today.iterrows():
sales_rows.append([
day,
row["DrugID"],
row["Drug_Name"],
qty_sold[idx],
row["Price"],
qty_sold[idx] * row["Price"]
])
df_med_sales = pd.DataFrame(
sales_rows,
columns=[
"Date",
"DrugID",
"Drug_Name",
"Quantity_Sold",
"Unit_Price",
"Total_Amount"
]
)
df_med_sales.head()
| Date | DrugID | Drug_Name | Quantity_Sold | Unit_Price | Total_Amount | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-01 | 142363 | Milrox 150mg Tablet | 13 | 55.0 | 715.0 |
| 1 | 2024-01-01 | 127868 | Lorsar H 50mg/12.5mg Tablet | 11 | 92.0 | 1012.0 |
| 2 | 2024-01-01 | 100693 | GMD OF Suspension | 13 | 35.0 | 455.0 |
| 3 | 2024-01-01 | 116398 | Kapinac SP Tablet | 4 | 69.0 | 276.0 |
| 4 | 2024-01-01 | 107081 | Ivaril DX Syrup | 13 | 79.0 | 1027.0 |
df_meds.head()
| Drug_Name | Manufacturer | Price | Active_Ingredient | Therapeutic_Class | Action_Class | Primary_Use | Is_Habit_Forming | Mfg_Date | Expiry_Date | Days_Remaining | Stock_Qty | DrugID | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Augmentin 625 Duo Tablet | Glaxo SmithKline Pharmaceuticals Ltd | 223.42 | Amoxycillin (500mg) | ANTI INFECTIVES | NaN | Treatment of Bacterial infections | No | 2025-07-22 | 2026-10-11 | 305 | 405 | 1 |
| 1 | Azithral 500 Tablet | Alembic Pharmaceuticals Ltd | 132.36 | Azithromycin (500mg) | ANTI INFECTIVES | Macrolides | Treatment of Bacterial infections | No | 2025-01-31 | 2028-01-31 | 782 | 280 | 2 |
| 2 | Ascoril LS Syrup | Glenmark Pharmaceuticals Ltd | 118.00 | Ambroxol (30mg/5ml) | RESPIRATORY | NaN | Treatment of Cough with mucus | No | 2024-11-10 | 2026-11-10 | 335 | 437 | 3 |
| 3 | Allegra 120mg Tablet | Sanofi India Ltd | 218.81 | Fexofenadine (120mg) | RESPIRATORY | H1 Antihistaminics (second Generation) | Treatment of Sneezing and runny nose due to al... | No | 2024-06-19 | 2025-12-18 | 8 | 171 | 4 |
| 4 | Allegra 120mg Tablet | Sanofi India Ltd | 218.81 | Fexofenadine (120mg) | RESPIRATORY | H1 Antihistaminics (second Generation) | Treatment of Sneezing and runny nose due to al... | No | 2024-02-16 | 2027-02-15 | 432 | 108 | 4 |
df_meds.to_csv('meds_inventory.csv',index=False)
df_med_sales.to_csv('med_sales.csv',index=False)
df_med_sales
| Date | DrugID | Drug_Name | Quantity_Sold | Unit_Price | Total_Amount | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-01 | 142363 | Milrox 150mg Tablet | 13 | 55.00 | 715.0 |
| 1 | 2024-01-01 | 127868 | Lorsar H 50mg/12.5mg Tablet | 11 | 92.00 | 1012.0 |
| 2 | 2024-01-01 | 100693 | GMD OF Suspension | 13 | 35.00 | 455.0 |
| 3 | 2024-01-01 | 116398 | Kapinac SP Tablet | 4 | 69.00 | 276.0 |
| 4 | 2024-01-01 | 107081 | Ivaril DX Syrup | 13 | 79.00 | 1027.0 |
| ... | ... | ... | ... | ... | ... | ... |
| 218752 | 2025-12-30 | 125990 | Lifobid Infusion | 13 | 120.00 | 1560.0 |
| 218753 | 2025-12-30 | 119969 | Lozep 2 Tablet | 6 | 27.55 | 165.3 |
| 218754 | 2025-12-30 | 218810 | Tamrik-D Tablet | 1 | 450.00 | 450.0 |
| 218755 | 2025-12-30 | 114203 | Kylazine Tablet | 11 | 36.00 | 396.0 |
| 218756 | 2025-12-30 | 246333 | Zesnil 25mg Tablet | 1 | 70.00 | 70.0 |
218757 rows × 6 columns
Loading into MySQL dataset¶
DB_USER = "root"
DB_PASSWORD = "Brahmapur@1234"
DB_HOST = "127.0.0.1"
DB_PORT = 3306
DB_NAME = "pharmac_inventory"
from urllib.parse import quote_plus
ENCODED_PASSWORD = quote_plus(DB_PASSWORD)
db_url = f"mysql+mysqlconnector://{DB_USER}:{ENCODED_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
engine = create_engine(db_url, echo=False)
file1 = 'med_sales.csv'
tab1 = 'med_sales'
file2 = 'meds_inventory.csv'
tab2 = 'meds_inventory'
def load_csv_to_mysql(file_path, table_name, engine):
try:
df = pd.read_csv(file_path)
df.to_sql(
name=table_name,
con=engine,
if_exists='replace',
index=False,
method='multi'
)
print(f"Loaded {file_path} into table `{table_name}` successfully")
except Exception as e:
print(f"An error occurred loading {file_path}: {e}")
if __name__ == "__main__":
load_csv_to_mysql(file1, tab1, engine)
load_csv_to_mysql(file2, tab2, engine)
engine.dispose()
Loaded med_sales.csv into table `med_sales` successfully Loaded meds_inventory.csv into table `meds_inventory` successfully
1.Which medicines frequently go out of stock?¶
Frequently stock-out drugs:¶
Clavoxin Dry Syrup – Antibiotic (used for bacterial infections in children)
Oral Polio Vaccine – Prevents poliomyelitis (essential immunization)
Zift 250mg Tablet – Antibiotic for respiratory & urinary infections
Arcip 500mg Tablet – Antibiotic, often used in seasonal infections
Homatropine Eye Drop – Used for eye inflammation and pain relief
Insight:
Most stock-outs occur in essential antibiotics, vaccines, and emergency medicines, directly impacting patient care.
df_meds = df_meds.sort_values(["DrugID", "Mfg_Date"])
df_meds["Mfg_Date"] = pd.to_datetime(df_meds["Mfg_Date"])
df_meds["Expiry_Date"] = pd.to_datetime(df_meds["Expiry_Date"])
df_med_sales["Date"] = pd.to_datetime(df_med_sales["Date"])
def get_valid_batch(drug_id, sale_date):
batches = df_meds[df_meds["DrugID"] == drug_id]
valid = batches[batches["Mfg_Date"] <= sale_date]
if valid.empty:
return None
return valid.sort_values("Mfg_Date").iloc[-1]
df_meds["BatchID"] = range(1, len(df_meds) + 1)
df_med_sales["SaleID"]=df_med_sales.index
df_med_sales=df_med_sales.merge(df_meds,on='DrugID',how='left')
df_med_sales = df_med_sales[(df_med_sales["Mfg_Date"] <= df_med_sales["Date"])&(df_med_sales['Expiry_Date']> df_med_sales['Date']) ]
df_med_sales = df_med_sales.sort_values(["SaleID", "Mfg_Date"])
df_med_sales = df_med_sales.drop_duplicates(subset="SaleID", keep="last") # keeping the latest batch as per the sales date and mfg date
# keeps the last occurance (row) from multiple merged rows for a sale id , drug id .
df_med_sales=df_med_sales[[
'Date', 'DrugID', 'Drug_Name_x', 'Quantity_Sold', 'Unit_Price',
'Total_Amount', 'SaleID',
'Mfg_Date', 'Expiry_Date',
'Stock_Qty', 'BatchID']]
df_med_sales.columns=['Date', 'DrugID', 'Drug_Name', 'Quantity_Sold', 'Unit_Price',
'Total_Amount', 'SaleID', 'Mfg_Date', 'Expiry_Date', 'Initial_Stock',
'BatchID']
df_med_sales=df_med_sales.sort_values(['DrugID','Date'])
df_med_sales["Cumulative_Sold"] = df_med_sales.groupby("DrugID")["Quantity_Sold"].cumsum()
df_med_sales['Remaining_Stock']=df_med_sales["Initial_Stock"] - df_med_sales["Cumulative_Sold"]
df_med_sales["Stockout_Flag"] = (df_med_sales["Remaining_Stock"] <= 0) | (df_med_sales['Remaining_Stock']< df_med_sales['Quantity_Sold'])
stockout = (
df_med_sales[df_med_sales["Stockout_Flag"] == True]
.groupby(["DrugID",'Drug_Name'])
.size()
.reset_index(name="Stockout_Count")
)
stockout.sort_values(by="Stockout_Count",ascending=False).head(20)
| DrugID | Drug_Name | Stockout_Count | |
|---|---|---|---|
| 6 | 56291 | Clavoxin Dry Syrup | 4 |
| 15 | 159603 | Oral Polio Vaccine | 3 |
| 20 | 244778 | Zift 250mg Tablet | 2 |
| 1 | 10811 | Arcip 500mg Tablet | 2 |
| 11 | 102166 | Homatropine Eye Drop | 2 |
| 12 | 103634 | Hepasure Infusion | 1 |
| 19 | 222705 | Theolar 40mg Tablet | 1 |
| 18 | 196486 | Symbiotik XL 625 Tablet | 1 |
| 17 | 182670 | Ringer Lactate Infusion | 1 |
| 16 | 180678 | Qualifest-T 1gm/0.125gm Injection | 1 |
| 14 | 149658 | NS 0.9% Infusion | 1 |
| 13 | 114691 | Kezop Soap | 1 |
| 0 | 110 | Alkasol Oral Solution Sugar Free | 1 |
| 9 | 82583 | Foracort 400 Rotacap | 1 |
| 8 | 74194 | Exol Syrup | 1 |
| 7 | 59031 | Diclolab 75mg Injection | 1 |
| 5 | 54358 | Cefidaz 50mg Tablet DT | 1 |
| 4 | 45689 | Chingasu 75mg Injection | 1 |
| 3 | 25293 | B Bact Ointment | 1 |
| 2 | 15881 | Ambrozen-S Syrup | 1 |
2.Sales Pattern for Top10 Fast moving drugs¶
Sales Pattern for Top 10 Fast-Moving Drugs¶
Clavoxin Dry Syrup shows consistent high demand throughout the year.
Arcip 500mg Tablet has demand spikes in winter and spring, indicating seasonal illness trends.
Insight:
Fast-moving drugs show clear seasonal and continuous demand patterns, which were previously not factored into stock planning.
top10 = stockout.head(10)["DrugID"]
fast_moving10 = df_med_sales[df_med_sales["DrugID"].isin(top10)]
import plotly.graph_objects as go
fig = go.Figure()
for drug in fast_moving10["Drug_Name"].unique():
sub = fast_moving10[fast_moving10["Drug_Name"] == drug]
fig.add_trace(
go.Scatter(
x=sub["Date"],
y=sub["Cumulative_Sold"],
mode="lines+markers",
name=drug,
hovertemplate=
"<b>Date:</b> %{x}<br>" +
"<b>Drug:</b> " + drug + "<br>" +
"<b>Units Sold:</b> %{y}<br>" +
"<b>Remaining Stock:</b> %{customdata}<extra></extra>",
customdata=sub["Remaining_Stock"]
)
)
fig.update_layout(
title="Daily Sales Pattern for Top 10 Fast-Moving Drugs",
xaxis_title="Date",
yaxis_title="Units Sold",
legend_title="Drug Name",
hovermode="closest",
height=600
)
fig.show()
df_med_sales["Month"] = df_med_sales["Date"].dt.to_period("M").astype(str)
stockout_matrix = (
df_med_sales[df_med_sales["Stockout_Flag"] == True]
.groupby(["Drug_Name", "Month"])
.size()
.reset_index(name="Stockout_Count")
)
pivot_df = stockout_matrix.pivot(index="Drug_Name", columns="Month", values="Stockout_Count").fillna(0)
fig = px.imshow(
pivot_df,
labels=dict(x="Month", y="Drug Name", color="Stockout Intensity"),
title="Heatmap: Drug vs Month — Stock-out Intensity",
aspect="auto",
color_continuous_scale="Reds"
)
fig.update_layout(
xaxis=dict(side="top"),
height=800
)
fig.show()
3.Which drugs are causing financial loss due to expiry?¶
Top contributors:¶
Keytruda Injection
Crizalk 200mg Capsule
Avastin 400mg Injection
Insight:
High-value specialty drugs cause disproportionately high losses even with small expiry volumes.
df_meds['Loss_by_expiry']= np.where(
df_meds["Days_Remaining"] < 0,
df_meds["Stock_Qty"] * df_meds["Price"] * -1,
0
)
expired_drugs=df_meds.groupby(['Drug_Name']).aggregate({'Loss_by_expiry':"sum"}).sort_values("Loss_by_expiry",ascending=True).reset_index()
expired_drugs.head(10)
| Drug_Name | Loss_by_expiry | |
|---|---|---|
| 0 | Keytruda Injection | -66929500.00 |
| 1 | Crizalk 200mg Capsule | -44378632.50 |
| 2 | Avastin 400mg Injection | -38816250.00 |
| 3 | Abiron 250mg Tablet | -32760000.00 |
| 4 | Herclon Injection | -32350406.25 |
| 5 | Neuzumab 440mg Injection | -26291250.00 |
| 6 | Alkeprost 250mg Tablet | -24403500.00 |
| 7 | Biceltis 440mg Injection | -21600000.00 |
| 8 | Bevacirel 400mg Injection | -19078462.94 |
| 9 | Nucala Injection | -15571140.00 |
4.1.Total Loss by Expiry¶
from IPython.display import HTML
total_loss_bn = df_meds["Loss_by_expiry"].sum() / 1_000_000000 * -1
HTML(f"<h1 style='font-size:40px; color:#d9534f;'>Total Loss by Expired drugs : {total_loss_bn:.2f} Billion</h1>")
Total Loss by Expired drugs : 1.67 Billion
4.2.Total Loss by stocking out¶
Observation¶
Stock-out losses are highest for essential, fast-moving medicines such as vaccines, antibiotics, and emergency infusions.
These drugs show high demand frequency, indicating under-forecasted consumption rather than excess inventory.
How to Reduce Stock-out Loss¶
1️⃣ Demand-Driven Reorder Planning
Classify these drugs as Fast-Moving / Critical Items
Maintain higher safety stock compared to regular medicines
2️⃣ Dynamic Reorder Alerts
Use Reorder Point (ROP) based alerts instead of fixed minimum stock
Trigger alerts when: Remaining Stock ≤ Reorder Point
3️⃣ Seasonal Adjustment
Increase stock levels for: Antibiotics during winter & infection-heavy seasons
Vaccines based on immunization schedules
4️⃣ Priority Supplier Contracts
- Ensure shorter lead times for: Vaccines,Emergency infusions,Pediatric medicines
takeaway¶
Fast-moving essential medicines require proactive, demand-driven restocking to prevent revenue loss and patient care disruption.
df_med_sales['Loss_by_stockout']= np.where(
df_med_sales["Stockout_Flag"]==True,
(df_med_sales["Quantity_Sold"]-df_med_sales['Remaining_Stock']) * df_med_sales["Unit_Price"] * -1 ,
0
)
stockout_loss=df_med_sales.groupby(['Drug_Name']).aggregate({'Loss_by_stockout':"sum"}).sort_values("Loss_by_stockout",ascending=True).reset_index()
stockout_loss.head(10)
| Drug_Name | Loss_by_stockout | |
|---|---|---|
| 0 | Oral Polio Vaccine | -9350.00 |
| 1 | Clavoxin Dry Syrup | -5815.00 |
| 2 | Qualifest-T 1gm/0.125gm Injection | -2960.00 |
| 3 | Symbiotik XL 625 Tablet | -1567.16 |
| 4 | Hepasure Infusion | -1000.00 |
| 5 | Arcip 500mg Tablet | -990.00 |
| 6 | Zift 250mg Tablet | -750.00 |
| 7 | Homatropine Eye Drop | -746.60 |
| 8 | Ringer Lactate Infusion | -696.08 |
| 9 | B Bact Ointment | -636.00 |
from IPython.display import HTML
total_loss_th = df_med_sales["Loss_by_stockout"].sum() / 1_000 * -1
HTML(f"<h1 style='font-size:40px; color:#d9534f;'>Total Loss by Stockout drugs: {total_loss_th:.2f} Thousand </h1>")
Total Loss by Stockout drugs: 27.34 Thousand
4.3 Top 10 drugs causing highest expiry loss¶
top_losses = expired_drugs.head(50)["Drug_Name"]
drugs_at_loss = df_meds[df_meds["Drug_Name"].isin(top_losses)]
df_top10 = drugs_at_loss.groupby("Drug_Name")["Loss_by_expiry"].sum().sort_values().head(20).reset_index()
fig = go.Figure()
fig.add_trace(
go.Bar(
x=df_top10["Loss_by_expiry"],
y=df_top10["Drug_Name"],
orientation='h',
marker_color='red'
)
)
fig.update_layout(
title="Top 10 Drugs at Loss by Expiry",
height=500,
xaxis_title="Loss Amount",
yaxis_title="Drug Name",
template="plotly_white"
)
fig.show()
5.Expiry Loss per therapeutic class¶
class_loss = (
drugs_at_loss.groupby(["Therapeutic_Class", "Drug_Name"])["Loss_by_expiry"]
.sum()
.head(50)
.reset_index()
)
fig = go.Figure()
for drug in class_loss["Drug_Name"].unique():
sub = class_loss[class_loss["Drug_Name"] == drug]
fig.add_trace(
go.Bar(
x=sub["Therapeutic_Class"],
y=sub["Loss_by_expiry"],
name=drug
)
)
fig.update_layout(
barmode='stack',
title="Stacked Bar Chart: Drugs at per Therapeutic Class",
width=1000,
height=700,
xaxis_title="Therapeutic Class",
yaxis_title="Total Loss",
template="plotly_white",
legend_title="Drug Name"
)
fig.show()
6.ABC Analysis¶
df_med_sales.drop('Total_Amount',axis=1,inplace=True)
df_med_sales['Total_Amount']=np.where(
(df_med_sales['Remaining_Stock']<df_med_sales['Quantity_Sold']) & (df_med_sales['Remaining_Stock'] >0),
df_med_sales['Remaining_Stock']*df_med_sales['Unit_Price'],
df_med_sales['Quantity_Sold']*df_med_sales['Unit_Price']
)
revenue_df = df_med_sales.groupby("DrugID", as_index=False).agg({"Drug_Name": "first","Total_Amount": "sum","Quantity_Sold": "sum"})
revenue_df.rename(columns={"Total_Amount": "Total_Revenue","Quantity_Sold": "Total_Quantity"}, inplace=True)
revenue_df = revenue_df.sort_values("Total_Revenue", ascending=False).reset_index(drop=True)
revenue_df["Cum_Revenue"] = revenue_df["Total_Revenue"].cumsum()
revenue_df["Cum_Revenue_Percent"] = 100 * revenue_df["Cum_Revenue"] / revenue_df["Total_Revenue"].sum()
assigning ABC category¶
def classify_abc(p):
if p <= 20:
return "A"
elif p <= 50:
return "B"
else:
return "C"
revenue_df["ABC_Category"] = revenue_df["Cum_Revenue_Percent"].apply(classify_abc)
abc_revenue = revenue_df.groupby("ABC_Category")["Total_Revenue"].sum().reset_index()
drug_names = (
revenue_df.groupby("ABC_Category")["Drug_Name"]
.apply(lambda x: ", ".join(sorted(x.unique())))
.reset_index()
.rename(columns={"Drug_Name": "Drug_List"})
)
abc_details = abc_revenue.merge(drug_names, on="ABC_Category")
abc_details['Category_count']= abc_details['Drug_List'].apply(len)
abc_details
| ABC_Category | Total_Revenue | Drug_List | Category_count | |
|---|---|---|---|---|
| 0 | A | 4.846973e+07 | ACTEMRA 400 MG INJECTION, Abatitor 250mg Table... | 907 |
| 1 | B | 7.355068e+07 | ACTEMRA 200 MG INJECTION, ACTEMRA 80 MG INJECT... | 12686 |
| 2 | C | 1.220735e+08 | A 1 5mg Tablet, A C Ford 200mg Tablet, A Clin ... | 2032846 |
Category A drugs are a small, high-impact group that together generate the top 20% of total revenue. These are business-critical items where availability directly affects revenue and patient care.
Category B drugs contribute a moderate share of revenue and require balanced monitoring to avoid both overstocking and shortages.
Category C drugs consist of a large number of low-value items, each contributing small revenue individually but adding to inventory handling and storage complexity.
Key Insight:¶
Revenue is highly concentrated i.e. a few drugs drive most financial performance, while many low-value items increase operational effort without proportional return.
This highlights the need to prioritize control, forecasting, and monitoring on Category A drugs, while simplifying management for Category C items.
fig = px.pie(
abc_details,
names="ABC_Category",
values="Total_Revenue",
title="Revenue Share by ABC Category",
hover_data={"Total_Revenue": True}
)
fig.update_traces(
hovertemplate=
"<b>Category:</b> %{label}<br>" +
"<b>Revenue:</b> ₹%{value:,.2f}<br>" +
"<b>Top</b> %{percent}Percent<br>"
)
fig.show()
7.Reorder Quantity Recommendations¶
Created Reorder_Table with:¶
- DrugID, Remaining_Stock, Reorder_Point, Reorder_Quantity
Insight:
Reorder quantities are calculated based on expected demand during lead time + safety stock, reducing emergency stock-outs.
df_med_sales['Days_Remaining']=df_med_sales['Expiry_Date']-df_med_sales['Date']
from scipy.stats import norm
Lead_Time_Days = 7
Target_Service_Level = 0.95 # 95% service level means only 5% chance of stockout
Z_Value = norm.ppf(Target_Service_Level)
Daily_Demand_Stats = (
df_med_sales
.groupby('DrugID')['Quantity_Sold']
.agg(
avg_daily_demand='mean',
daily_demand_std='std'
)
.reset_index()
)
Daily_Demand_Stats['daily_demand_std']=Daily_Demand_Stats['daily_demand_std'].fillna(0)
Daily_Demand_Stats['Demand_Mean_LeadTime'] = (
Daily_Demand_Stats['avg_daily_demand'] * Lead_Time_Days
)
Daily_Demand_Stats['Demand_StdDev_LeadTime'] = (
Daily_Demand_Stats['daily_demand_std'] * np.sqrt(Lead_Time_Days)
)
Daily_Demand_Stats['Safety_Stock'] = (
Z_Value * Daily_Demand_Stats['Demand_StdDev_LeadTime']
)
Daily_Demand_Stats['Reorder_Point'] = (
Daily_Demand_Stats['Demand_Mean_LeadTime'] +
Daily_Demand_Stats['Safety_Stock']
)
Current_Stock = df_med_sales[['DrugID','Drug_Name','Remaining_Stock']].drop_duplicates('DrugID')
Full_Stock_Model = Daily_Demand_Stats.merge(Current_Stock,on='DrugID',how='left')
Full_Stock_Model['Suggested_Reorder_Qty'] = np.maximum(
0,
Full_Stock_Model['Demand_Mean_LeadTime'] +
Full_Stock_Model['Safety_Stock'] -
Full_Stock_Model['Remaining_Stock']
)
Full_Stock_Model[
['Demand_Mean_LeadTime', 'Demand_StdDev_LeadTime',
'Safety_Stock', 'Reorder_Point', 'Suggested_Reorder_Qty']
] = Full_Stock_Model[
['Demand_Mean_LeadTime', 'Demand_StdDev_LeadTime',
'Safety_Stock', 'Reorder_Point', 'Suggested_Reorder_Qty']
].round().astype(int)
Full_Stock_Model.sort_values(by='Suggested_Reorder_Qty',ascending=False)
| DrugID | avg_daily_demand | daily_demand_std | Demand_Mean_LeadTime | Demand_StdDev_LeadTime | Safety_Stock | Reorder_Point | Drug_Name | Remaining_Stock | Suggested_Reorder_Qty | |
|---|---|---|---|---|---|---|---|---|---|---|
| 85021 | 242323 | 14.0 | 0.000000 | 98 | 0 | 0 | 98 | Zithium XL 100mg Suspension | 26 | 72 |
| 33663 | 96001 | 14.0 | 0.000000 | 98 | 0 | 0 | 98 | Glimicer M2 Forte Tablet SR | 26 | 72 |
| 53112 | 151371 | 14.0 | 0.000000 | 98 | 0 | 0 | 98 | Ndase Tablet | 26 | 72 |
| 5618 | 15881 | 14.0 | 0.000000 | 98 | 0 | 0 | 98 | Ambrozen-S Syrup | 26 | 72 |
| 42471 | 120945 | 14.0 | 0.000000 | 98 | 0 | 0 | 98 | Levroxa Syrup | 26 | 72 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 30068 | 85612 | 3.0 | 0.000000 | 21 | 0 | 0 | 21 | Fenase Tablet | 469 | 0 |
| 30067 | 85611 | 6.0 | 0.000000 | 42 | 0 | 0 | 42 | Fensaide P 50mg/500mg Tablet | 466 | 0 |
| 30066 | 85607 | 9.0 | 0.000000 | 63 | 0 | 0 | 63 | Flura 200mg Tablet | 403 | 0 |
| 30065 | 85602 | 4.0 | 0.000000 | 28 | 0 | 0 | 28 | Finide 180mg Tablet | 326 | 0 |
| 87477 | 249397 | 7.0 | 6.082763 | 49 | 16 | 26 | 75 | ZI Fast 500mg Injection | 312 | 0 |
87478 rows × 10 columns
Safety_Stock¶
Extra buffer stock to protect against demand spikes and delivery delays.
Reorder_Point (ROP)¶
Stock level at which a new order must be placed to avoid stock-outs.
Remaining_Stock¶
Current available inventory for the drug.
Suggested_Reorder_Qty (Reorder_Quantity)¶
Quantity recommended for restocking to safely meet upcoming demand.
Reorder quantities are calculated using expected demand during lead time plus safety stock, ensuring continuity of supply.
This approach reduces emergency stock-outs while preventing unnecessary over-stocking and expiry losses.
Full_Stock_Model['Suggested_Reorder_Qty'] = Full_Stock_Model['Suggested_Reorder_Qty'].clip(lower=0)
7.1.How much quantity should be reordered for each drug to prevent stock-outs?¶
Reorder_Table = Full_Stock_Model[['DrugID', 'Remaining_Stock','Reorder_Point','Suggested_Reorder_Qty']].sort_values('Suggested_Reorder_Qty',ascending=False)
Reorder_Table
| DrugID | Remaining_Stock | Reorder_Point | Suggested_Reorder_Qty | |
|---|---|---|---|---|
| 85021 | 242323 | 26 | 98 | 72 |
| 33663 | 96001 | 26 | 98 | 72 |
| 53112 | 151371 | 26 | 98 | 72 |
| 5618 | 15881 | 26 | 98 | 72 |
| 42471 | 120945 | 26 | 98 | 72 |
| ... | ... | ... | ... | ... |
| 30068 | 85612 | 469 | 21 | 0 |
| 30067 | 85611 | 466 | 42 | 0 |
| 30066 | 85607 | 403 | 63 | 0 |
| 30065 | 85602 | 326 | 28 | 0 |
| 87477 | 249397 | 312 | 75 | 0 |
87478 rows × 4 columns
8.Reorder Flagging, when Remaining_Stock gets close to i.e. <= Reorder_Point¶
df_med_sales=df_med_sales.merge(
Full_Stock_Model[['DrugID','Demand_Mean_LeadTime','Reorder_Point','Safety_Stock']],
on='DrugID',
how='left'
)
df_med_sales['Reorder_Flag'] = (
df_med_sales['Remaining_Stock'] <= df_med_sales['Reorder_Point']
)
df_med_sales['Reorder_Quantity']=(
df_med_sales['Demand_Mean_LeadTime'] +
df_med_sales['Safety_Stock'] -
df_med_sales['Remaining_Stock']
)
reorder_df=df_med_sales[['Date','DrugID','Drug_Name','Quantity_Sold','Remaining_Stock','Stockout_Flag','Reorder_Point','Reorder_Flag','Reorder_Quantity']]
reorder_df=reorder_df.merge(
df_meds[['DrugID','Manufacturer']],
on='DrugID',
how='left'
)
reorder_df
| Date | DrugID | Drug_Name | Quantity_Sold | Remaining_Stock | Stockout_Flag | Reorder_Point | Reorder_Flag | Reorder_Quantity | Manufacturer | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2025-12-19 | 1 | Augmentin 625 Duo Tablet | 6 | 399 | False | 42 | False | -357 | Glaxo SmithKline Pharmaceuticals Ltd |
| 1 | 2024-09-10 | 4 | Allegra 120mg Tablet | 5 | 117 | False | 67 | False | -50 | Sanofi India Ltd |
| 2 | 2024-09-10 | 4 | Allegra 120mg Tablet | 5 | 117 | False | 67 | False | -50 | Sanofi India Ltd |
| 3 | 2024-09-10 | 4 | Allegra 120mg Tablet | 5 | 117 | False | 67 | False | -50 | Sanofi India Ltd |
| 4 | 2024-09-10 | 4 | Allegra 120mg Tablet | 5 | 117 | False | 67 | False | -50 | Sanofi India Ltd |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 132153 | 2025-03-23 | 249389 | Zunix 50mg Dry Syrup | 13 | 58 | False | 91 | True | 33 | Vesnik Molecules |
| 132154 | 2024-10-06 | 249390 | Ziyapod 200mg Tablet DT | 6 | 289 | False | 42 | False | -247 | Ziyana Lifesciences Pvt Ltd |
| 132155 | 2024-09-08 | 249397 | ZI Fast 500mg Injection | 4 | 312 | False | 75 | False | -237 | Burgeon Health Series Private Limited |
| 132156 | 2024-10-16 | 249397 | ZI Fast 500mg Injection | 14 | 298 | False | 75 | False | -223 | Burgeon Health Series Private Limited |
| 132157 | 2025-03-18 | 249397 | ZI Fast 500mg Injection | 3 | 295 | False | 75 | False | -220 | Burgeon Health Series Private Limited |
132158 rows × 10 columns
8.1.Reorder Trigger Heatmap — Top 20 Fast-Moving Drugs¶
fast20 = (
df_med_sales.groupby("DrugID")["Quantity_Sold"]
.sum()
.sort_values(ascending=False)
.head(20)
.reset_index()
)
df_final=reorder_df.merge(fast20['DrugID'],on='DrugID',how='right')
df_final["Reorder_Intensity"] = (
(df_final["Reorder_Point"] - df_final["Remaining_Stock"])
/ df_final["Reorder_Point"]
).clip(lower=0) # negative becomes 0
df_final.head(3)
| Date | DrugID | Drug_Name | Quantity_Sold | Remaining_Stock | Stockout_Flag | Reorder_Point | Reorder_Flag | Reorder_Quantity | Manufacturer | Reorder_Intensity | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-03-19 | 182670 | Ringer Lactate Infusion | 9 | 101 | False | 73 | False | -28 | Baxter India Pvt Ltd | 0.0 |
| 1 | 2024-03-19 | 182670 | Ringer Lactate Infusion | 9 | 101 | False | 73 | False | -28 | Parenteral Drugs India Ltd | 0.0 |
| 2 | 2024-03-19 | 182670 | Ringer Lactate Infusion | 9 | 101 | False | 73 | False | -28 | Parenteral Drugs India Ltd | 0.0 |
df_final["Month"] = df_final["Date"].dt.to_period("M").astype(str)
pivot_df = df_final.pivot_table(
index="Drug_Name",
columns="Month",
values="Reorder_Intensity",
aggfunc="mean"
)
fig = px.imshow(
pivot_df,
labels=dict(
x="Month",
y="Drug Name",
color="Reorder Intensity (0–1)"
),
title="Reorder Trigger Heatmap — Top 20 Fast-Moving Drugs",
aspect="auto",
color_continuous_scale=[
[0.0, "#2166ac"],
[0.5, "#ffff33"],
[1.0, "#b2182b"]
] # Blue = safe , yellow=average , Red = danger
)
fig.update_traces(
hovertemplate=
"<b>Drug:</b> %{y}<br>" +
"<b>Month:</b> %{x}<br>" +
"<b>Reorder Intensity:</b> %{z:.2f}<br>" +
"<extra></extra>"
)
fig.update_layout(
xaxis=dict(side="top"),
height=850,
coloraxis_colorbar=dict(
title="Risk",
ticks="outside"
)
)
fig.show()
df_final['Date'] = pd.to_datetime(df_final['Date'])
df_final['Month'] = df_final['Date'].dt.to_period('M').astype(str)
df_plot = df_final.copy()
df_plot['Reorder_Intensity'] = (
(df_plot['Reorder_Point'] - df_plot['Remaining_Stock']) / df_plot['Reorder_Point']
).clip(lower=0, upper=1)
pivot_columns = {
"Reorder_Intensity": "mean",
"Quantity_Sold": "sum",
"Remaining_Stock": "mean",
"Reorder_Point": "mean",
"Reorder_Quantity": "mean"
}
pivots = {}
for col, func in pivot_columns.items():
pivots[col] = df_plot.pivot_table(
index="Drug_Name",
columns="Month",
values=col,
aggfunc=func
)
base = pivots["Reorder_Intensity"]
for col in pivot_columns:
pivots[col] = pivots[col].reindex(index=base.index, columns=base.columns)
manufacturer_map = (
df_plot.drop_duplicates("Drug_Name")
.set_index("Drug_Name")["Manufacturer"]
)
manufacturers_matrix = np.array([
[manufacturer_map.get(drug, "")] * len(base.columns)
for drug in base.index
])
reorder_flag = np.where(base.values > 0, "TRUE", "FALSE")
customdata = np.dstack([
reorder_flag,
manufacturers_matrix,
pivots["Remaining_Stock"].values,
pivots["Reorder_Point"].values,
pivots["Reorder_Quantity"].values
])
fig = px.imshow(
base,
labels=dict(x="Month", y="Drug Name", color="Reorder Intensity (0–1)"),
title="Reorder Risk Heatmap — Top 20 Fast-Moving Drugs",
aspect="auto",
color_continuous_scale=[
[0.0, "#2166ac"],
[0.5, "#ffff33"],
[1.0, "#b2182b"]
]
)
fig.data[0].customdata = customdata
fig.update_traces(
hovertemplate=(
"<b>Drug:</b> %{y}<br>"
"<b>Month:</b> %{x}<br>"
"<b>Reorder Flag:</b> %{customdata[0]}<br>"
"<b>Manufacturer:</b> %{customdata[1]}<br>"
"<b>Remaining Stock:</b> %{customdata[2]}<br>"
"<b>Reorder Point (ROP):</b> %{customdata[3]}<br>"
"<b>Suggested Reorder Qty (month avg):</b> %{customdata[4]}<extra></extra>"
)
)
fig.update_layout(
xaxis=dict(side="top"),
height=850
)
fig.show()